﻿using Business.Service;
using Model;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcAppWeb.Controllers
{
    public class EstimateProductionInfoController : BaseDataController
    {
        //
        // GET: /DeliveryBox/
        public ActionResult EstimateProductionInfo()
        {
            if (Session["UserInfo"] == null) return RedirectToAction("LogOn", "Account");
            else
            {
                AppUserList model = (AppUserList)Session["UserInfo"];
                return View(model);

            }
        }



        ///<summary>
        ///查询预估订单信息
        ///</summary>
        ///<returns></returns>
        public JsonResult InitJsonResult(string importBatch, string startDate, string endDate)
        {
            string where = "  where 1=1 ";
            int page = int.Parse(Request["page"].ToString());
            int rows = int.Parse(Request["rows"].ToString());
            int total = 0;
            if (importBatch != "")
            {
                where += " and l.importBatch='" + importBatch + "'";
            }
            else
            {
                if (startDate != "" && endDate != "")
                {
                    where += " and l.CREATEDATE BETWEEN to_date('" + startDate + "','yyyy-MM-dd')   AND to_date('" + endDate + " 23:59:59 ','yyyy-mm-dd hh24:mi:ss')";
                }
                else
                {
                    where += " and l.CREATEDATE BETWEEN  to_date('" + DateTime.Now.ToString("yyyy-MM-dd") + "','yyyy-MM-dd') AND to_date('" + DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59 ','yyyy-mm-dd hh24:mi:ss')";
                }
            }
            where += "  order by l.CREATEDATE desc";
            var retData = new object();
            BEstimateProduction db = new BEstimateProduction();
            retData = db.GetEstimateProductionInfoList(page, rows, out total, where).ToList();
            Dictionary<string, object> json = new Dictionary<string, object>();
            json.Add("total", total);
            json.Add("rows", retData);
            return Json(json);
        }


        /// <summary>
        /// 导入预估订单信息
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public string ImportEstimateProductionInfo(string dispatchingType)
        {
            string sjson = string.Empty;
            try
            {
                List<EstimateProductionInfo> list = new List<EstimateProductionInfo>();
                HttpPostedFileBase file = Request.Files["files"];
                string importStr = string.Empty;
                DataTable table = new DataTable();
                sjson = ImportFile(file, out table, out importStr);
                BEstimateProduction db = new BEstimateProduction();
                AppUserList model = (AppUserList)Session["UserInfo"];
                string batchno = DateTime.Now.Year.ToString() + DateTime.Now.Month + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();


                for (int i = 0; i < table.Rows.Count; i++)
                {
                    EstimateProductionInfo einfo = new EstimateProductionInfo();
                    if (table.Rows[i][0] != null && table.Rows[i][0].ToString().Trim() != "")
                    {
                        einfo.GOODSCODE = table.Rows[i][0].ToString().Trim();
                        einfo.GOODSNAME = table.Rows[i][1].ToString().Trim();
                        einfo.ESTIMATENUMBER = Convert.ToInt32(table.Rows[i][2].ToString().Trim());
                        einfo.IMPORTBATCH = batchno;
                        einfo.OPRNO = model.USERNAME;
                        einfo.UPDATENUMBER = 0;
                        einfo.ORDERNUMBER = 0;
                        einfo.ACTUALNUMBER = 0;
                        einfo.STATUS = "N";
                        einfo.CREATEDATE = "";
                        einfo.ESTIMATEDATE = table.Rows[i][3].ToString().Trim();
                        einfo.DISPATCHINGTYPE = dispatchingType;
                        list.Add(einfo);

                    }

                }
                bool bl = db.InsertEstimateProduction(list);
                if (bl)
                {
                    //db.InsertLmEstNumber(batchno);
                    sjson = "{success:true,batch:'" + batchno + "'}";
                }
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;
        }

        /// <summary>
        /// 匹配订单数量
        /// </summary>
        /// <param name="importBatch"></param>
        /// <param name="type">1.当日达，2.次日达</param>
        /// <returns></returns>
        public string MatchingEstimateProductionInfo(string importBatch, string type)
        {
            string sjson = string.Empty;
            BEstimateProduction db = new BEstimateProduction();
            string where = "  where 1=1 ";

            int total = 0;
            int n = 0;
            where += " and importBatch='" + importBatch + "'";

            var retData = new object();

            List<EstimateProductionInfo> list = db.GetEstimateProductionInfoList(1, 20, out total, where).ToList();
            if (list.Count > 0)
            {
                if (type == "1")
                {
                    string staDate = DateTime.Parse(list[0].ESTIMATEDATE).AddDays(-1).ToString("yyyy-MM-dd") + " 22:00";
                    string endDate = DateTime.Parse(list[0].ESTIMATEDATE).ToString("yyyy-MM-dd") + " 11:00";
                    n = db.UpdateOrderProductionInfoByDrd(DateTime.Parse(list[0].ESTIMATEDATE).ToString("yyyy-MM-dd"), staDate, endDate, importBatch);
                }
                else
                {
                    n = db.UpdateOrderProductionInfoByCrd(DateTime.Parse(list[0].ESTIMATEDATE).ToString("yyyy-MM-dd"), importBatch);
                }
                if (n > 0)
                {
                    sjson = "{success:true,batch:'" + importBatch + "'}";

                }
                else
                {
                    sjson = "{success:false,msg:'匹配失败'}";
                }
            }


            return sjson;
        }

        [HttpPost]
        /// <summary>
        /// 更新预估订单信息
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public string UpdateEstimateProductionInfo(string lmeID, string status)
        {
            string sjson = string.Empty;
            string updateNumber = Request["txtUPDATENUMBER"];
            string actualNumber = Request["txtACTUALNUMBER"];

            BEstimateProduction db = new BEstimateProduction();
            EstimateProductionInfo einfo = new EstimateProductionInfo();
            einfo.LMEID = Convert.ToInt32(lmeID);
            einfo.UPDATENUMBER = Convert.ToInt32(updateNumber);
            einfo.ACTUALNUMBER = Convert.ToInt32(actualNumber);
            einfo.STATUS = status;
            int n = db.UpdateEstimateProductionInfo(einfo);
            if (n > 0)
            {
                sjson = "{success:true,msg:'修改成功'}";
            }
            else
            {
                sjson = "{success:true,msg:'修改失败'}";
            }
            return sjson;
        }


        [HttpPost]
        /// <summary>
        /// 审核预购订单
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public string UpdateEstimateStatus()
        {
            string sjson = string.Empty;
            string importBatch = Request["txtDBBCBATCHNO"];

            BEstimateProduction db = new BEstimateProduction();

            int n = db.UpdateEstimateStatus(importBatch);

            if (n > 0)
            {
                sjson = "{success:true,msg:'修改成功'}";
            }
            else
            {
                sjson = "{success:true,msg:'修改失败'}";
            }

            return sjson;
        }


        /// <summary>
        /// 导出预估生产单、领料单、加工单信息
        /// </summary>
        /// <param name="dbbcBatchNo"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public FileResult ExporterExcel(string importBatch, string startDate, string endDate)
        {

            #region 获取数据源
            string where = "  where 1=1 ";

            int total = 0;
            if (importBatch != "")
            {
                where += " and importBatch='" + importBatch + "'";
            }
            else
            {
                if (startDate != "" && endDate != "")
                {
                    where += " and CREATEDATE BETWEEN  to_date('" + startDate + "','yyyy-MM-dd') AND to_date('" + endDate + " 23:59:59 ','yyyy-mm-dd hh24:mi:ss')";
                }
                else
                {
                    where += " and CREATEDATE BETWEEN  to_date('" + DateTime.Now.Date.ToString() + "','yyyy-MM-dd') AND to_date('" + DateTime.Now.Date.ToString("yyyy-MM-dd") + " 23:59:59 ','yyyy-mm-dd hh24:mi:ss')";
                }

            }

            BEstimateProduction db = new BEstimateProduction();

            List<EstimateProductionInfo> list = null;
            List<ReceiveMaterialInfo> listRM = null;
            List<ReceiveMaterialInfo> listRMByCJG = null;
            List<ReceiveMachSchemeInfo> listReceiveMachScheme = null;
            List<ReceiveMachSchemeInfo> listReceiveMachSchemeJC = null;
            List<ReceiveMachSchemeInfo> listReceiveMachSchemeFJC = null;
            if ((startDate != "" && endDate != "") && importBatch == "")
            {
                list = db.GetEstimateProductionInfoByDate(startDate, endDate + " 23:59").ToList();//获取导入预生产信息
                listRM = db.GetReceiveMaterialInfoByDate(startDate, endDate + " 23:59").ToList();//获取领料单信息
                listReceiveMachScheme = db.GetReceiveMachSchemeInfoByDate(startDate, endDate + " 23:59").OrderBy(i => i.GOODSCODE).ThenBy(i => i.DGOODSCODE).ToList();//获取预生产加工单信息
            }
            else
            {
                list = db.GetEstimateProductionInfoList(1, 50000, out total, where).ToList();//获取导入预生产信息
                listRM = db.GetReceiveMaterialInfoByQP(importBatch).ToList();//获取领料单信息
                listRMByCJG = db.GetReceiveMaterialInfoByCJG(importBatch).ToList();//获取领料单信息
                listReceiveMachScheme = db.GetReceiveMachSchemeInfo(importBatch).OrderBy(i => i.GOODSCODE).ThenBy(i => i.DGOODSCODE).ToList();//获取预生产加工单信息
                listReceiveMachSchemeJC = db.GetReceiveMachSchemeInfoByJC(importBatch).OrderBy(i => i.GOODSCODE).ThenBy(i => i.DGOODSCODE).ToList();//获取预生产加工单信息
                listReceiveMachSchemeFJC = db.GetReceiveMachSchemeInfoByFJC(importBatch).OrderBy(i => i.GOODSCODE).ThenBy(i => i.DGOODSCODE).ToList();//获取预生产加工单信息
            }
            #endregion

            #region 导出Excel

            //创建Excel文件的对象
            HSSFWorkbook book = new HSSFWorkbook();
            //添加一个sheet
            ISheet addr = book.CreateSheet("预估单");
            ISheet addrEstimateCJG = book.CreateSheet("称料表粗加工");
            ISheet addrEstimate = book.CreateSheet("称料表切配打包");

            ISheet addrMachScheme = book.CreateSheet("汇总加工单");
            ISheet addrMachSchemeJC = book.CreateSheet("净菜加工单");
            ISheet addrMachSchemeFJC = book.CreateSheet("非净菜加工单");

            ICellStyle style = book.CreateCellStyle();
            style.BorderBottom = BorderStyle.DashDot;
            style.BorderLeft = BorderStyle.DashDot;
            style.BorderRight = BorderStyle.DashDot;
            style.BorderTop = BorderStyle.DashDot;

            #region 预估单
            for (int j = 0; j < (list.Count + 1); j++)
            {
                addr.CreateRow(j);
                addr.GetRow(j).Height = 400;

                if (j == 0)
                {

                    addr.GetRow(0).CreateCell(0).SetCellValue("库位");
                    addr.GetRow(0).CreateCell(1).SetCellValue("货号");
                    addr.GetRow(0).CreateCell(2).SetCellValue("品名");
                    addr.GetRow(0).CreateCell(3).SetCellValue("预估数量");
                    addr.GetRow(0).CreateCell(4).SetCellValue("订单数量");
                    addr.GetRow(0).CreateCell(5).SetCellValue("修改数量");
                    addr.GetRow(0).CreateCell(6).SetCellValue("差异数量");
                }
                else
                {
                    double czNumber = 0;
                    if (Convert.ToInt32(list[j - 1].UPDATENUMBER) > 0)
                    {
                        czNumber = Convert.ToInt32(list[j - 1].ESTIMATENUMBER) - Convert.ToInt32(list[j - 1].UPDATENUMBER);
                    }
                    else
                    {
                        czNumber = Convert.ToInt32(list[j - 1].ESTIMATENUMBER) - Convert.ToInt32(list[j - 1].ORDERNUMBER);
                    }

                    addr.GetRow(j).CreateCell(0).SetCellValue(list[j - 1].LOOKUP);
                    addr.GetRow(j).CreateCell(1).SetCellValue(list[j - 1].GOODSCODE);
                    addr.GetRow(j).CreateCell(2).SetCellValue(list[j - 1].GOODSNAME);
                    addr.GetRow(j).CreateCell(3).SetCellValue(Convert.ToInt32(list[j - 1].ESTIMATENUMBER));
                    addr.GetRow(j).CreateCell(4).SetCellValue(Convert.ToInt32(list[j - 1].ORDERNUMBER));
                    addr.GetRow(j).CreateCell(5).SetCellValue(Convert.ToInt32(list[j - 1].UPDATENUMBER));
                    addr.GetRow(j).CreateCell(6).SetCellValue(Convert.ToInt32(czNumber));
                }

            }
            #endregion

            #region 领料单粗加工


            for (int i = 0; i < listRMByCJG.Count + 1; i++)
            {
                addrEstimateCJG.CreateRow(i);
                addrEstimateCJG.GetRow(i).Height = 400;

                if (i == 0)
                {
                    addrEstimateCJG.GetRow(0).CreateCell(0).SetCellValue("货位");
                    addrEstimateCJG.GetRow(0).CreateCell(1).SetCellValue("自定义库位");
                    addrEstimateCJG.GetRow(0).CreateCell(2).SetCellValue("库位");
                    addrEstimateCJG.GetRow(0).CreateCell(3).SetCellValue("货号");
                    addrEstimateCJG.GetRow(0).CreateCell(4).SetCellValue("品名");
                    addrEstimateCJG.GetRow(0).CreateCell(5).SetCellValue("规格");
                    addrEstimateCJG.GetRow(0).CreateCell(6).SetCellValue("单位");
                    addrEstimateCJG.GetRow(0).CreateCell(7).SetCellValue("领料量");
                }
                else if (i > 0)
                {
                    addrEstimateCJG.GetRow(i).CreateCell(0).SetCellValue(listRMByCJG[i - 1].LOCATIONCODE);
                    addrEstimateCJG.GetRow(i).CreateCell(1).SetCellValue(listRMByCJG[i - 1].DLOOKUP);
                    addrEstimateCJG.GetRow(i).CreateCell(2).SetCellValue(listRMByCJG[i - 1].LOOKUP);
                    addrEstimateCJG.GetRow(i).CreateCell(3).SetCellValue(listRMByCJG[i - 1].GOODSCODE);
                    addrEstimateCJG.GetRow(i).CreateCell(4).SetCellValue(listRMByCJG[i - 1].GOODSNAME);
                    addrEstimateCJG.GetRow(i).CreateCell(5).SetCellValue(listRMByCJG[i - 1].GOODSSPEC);
                    addrEstimateCJG.GetRow(i).CreateCell(6).SetCellValue(listRMByCJG[i - 1].UNITNAME);
                    addrEstimateCJG.GetRow(i).CreateCell(7).SetCellValue(Math.Round(Convert.ToDouble(listRMByCJG[i - 1].SUMQTY), 1));

                }
            }
            #endregion

            #region 领料单切配打包
            Hashtable hshTableEstimate = new Hashtable(); //  创建哈希表 
            Hashtable hshTableEstimateSumQty = new Hashtable(); //  创建哈希表 




            for (int i = 0; i < listRM.Count + 1; i++)
            {
                addrEstimate.CreateRow(i);
                addrEstimate.GetRow(i).Height = 400;

                if (i == 0)
                {
                    addrEstimate.GetRow(0).CreateCell(0).SetCellValue("自定义库位");
                    addrEstimate.GetRow(0).CreateCell(1).SetCellValue("货号");
                    addrEstimate.GetRow(0).CreateCell(2).SetCellValue("品名");
                    addrEstimate.GetRow(0).CreateCell(3).SetCellValue("规格");
                    addrEstimate.GetRow(0).CreateCell(4).SetCellValue("单位");
                    addrEstimate.GetRow(0).CreateCell(5).SetCellValue("单位领料量");
                    addrEstimate.GetRow(0).CreateCell(6).SetCellValue("领料量/KG");
                }
                else if (i > 0)
                {

                    addrEstimate.GetRow(i).CreateCell(0).SetCellValue(listRM[i - 1].LOOKUP);
                    addrEstimate.GetRow(i).CreateCell(1).SetCellValue(listRM[i - 1].DGOODSCODE);
                    addrEstimate.GetRow(i).CreateCell(2).SetCellValue(listRM[i - 1].DGOODSNAME);
                    addrEstimate.GetRow(i).CreateCell(3).SetCellValue(listRM[i - 1].DGOODSSPEC);
                    addrEstimate.GetRow(i).CreateCell(4).SetCellValue(listRM[i - 1].DUNITNAME);
                    addrEstimate.GetRow(i).CreateCell(5).SetCellValue(Convert.ToDouble(listRM[i - 1].SUMQTY / listRM[i - 1].DDPKGLOSSQTY));
                    addrEstimate.GetRow(i).CreateCell(6).SetCellValue(Math.Round(Convert.ToDouble(listRM[i - 1].SUMQTY / 1000), 1));
                }
            }
            #endregion

            #region 加工单
            Hashtable hshTable = new Hashtable(); //  创建哈希表 
            for (int k = 0; k < listReceiveMachScheme.Count + 1; k++)
            {
                addrMachScheme.CreateRow(k);
                addrMachScheme.GetRow(k).Height = 400;

                if (k == 0)
                {
                    addrMachScheme.GetRow(0).CreateCell(0).SetCellValue("库位");
                    addrMachScheme.GetRow(0).CreateCell(1).SetCellValue("货号");
                    addrMachScheme.GetRow(0).CreateCell(2).SetCellValue("品名");
                    addrMachScheme.GetRow(0).CreateCell(3).SetCellValue("原材料库位");
                    addrMachScheme.GetRow(0).CreateCell(4).SetCellValue("主、配、原料");
                    addrMachScheme.GetRow(0).CreateCell(5).SetCellValue("半成品单位");
                    addrMachScheme.GetRow(0).CreateCell(6).SetCellValue("请购份数");

                    addrMachScheme.GetRow(0).CreateCell(7).SetCellValue("成品净含量");
                    addrMachScheme.GetRow(0).CreateCell(8).SetCellValue("配方需要数量");
                    addrMachScheme.GetRow(0).CreateCell(9).SetCellValue("每份量/克(净)");
                    addrMachScheme.GetRow(0).CreateCell(10).SetCellValue("生成总量(净)");
                    addrMachScheme.GetRow(0).CreateCell(11).SetCellValue("差异总量(净)");
                    addrMachScheme.GetRow(0).CreateCell(12).SetCellValue("档口");



                }
                else if (k > 0)
                {

                    addrMachScheme.GetRow(k).CreateCell(0).SetCellValue(listReceiveMachScheme[k - 1].LOOKUP);
                    addrMachScheme.GetRow(k).CreateCell(1).SetCellValue(listReceiveMachScheme[k - 1].GOODSCODE);
                    addrMachScheme.GetRow(k).CreateCell(2).SetCellValue(listReceiveMachScheme[k - 1].GOODSNAME);
                    addrMachScheme.GetRow(k).CreateCell(3).SetCellValue(listReceiveMachScheme[k - 1].CUSTOMLOOKUP);
                    addrMachScheme.GetRow(k).CreateCell(4).SetCellValue(listReceiveMachScheme[k - 1].DGOODSNAME);
                    addrMachScheme.GetRow(k).CreateCell(5).SetCellValue(listReceiveMachScheme[k - 1].DPURUNITNAME);
                    addrMachScheme.GetRow(k).CreateCell(6).SetCellValue(Convert.ToInt32(listReceiveMachScheme[k - 1].ESTIMATENUMBER));
                    addrMachScheme.GetRow(k).CreateCell(7).SetCellValue(listReceiveMachScheme[k - 1].GOODSSPEC);
                    addrMachScheme.GetRow(k).CreateCell(8).SetCellValue(Convert.ToDouble(listReceiveMachScheme[k - 1].QTY));

                    addrMachScheme.GetRow(k).CreateCell(9).SetCellValue(Convert.ToDouble(listReceiveMachScheme[k - 1].DPKGLOSSQTY));
                    addrMachScheme.GetRow(k).CreateCell(10).SetCellValue(Convert.ToDouble(listReceiveMachScheme[k - 1].DPKGLOSSQTY * listReceiveMachScheme[k - 1].ESTIMATENUMBER));
                    addrMachScheme.GetRow(k).CreateCell(11).SetCellValue(Convert.ToDouble(listReceiveMachScheme[k - 1].DPKGLOSSQTY * listReceiveMachScheme[k - 1].ACTUALNUMBER));
                    addrMachScheme.GetRow(k).CreateCell(12).SetCellValue(listReceiveMachScheme[k - 1].GOODSDOOR);





                    if (k > 1)
                    {
                        string staCount = "";
                        int endCount = 0;
                        if (listReceiveMachScheme[k - 1].GOODSCODE.ToString() == listReceiveMachScheme[k - 2].GOODSCODE.ToString())
                        {
                            if (!hshTable.Contains(listReceiveMachScheme[k - 2].GOODSCODE.ToString()))
                            {
                                hshTable.Add(listReceiveMachScheme[k - 2].GOODSCODE.ToString(), (k - 1).ToString());
                            }

                            endCount = k;
                            staCount = (string)hshTable[listReceiveMachScheme[k - 1].GOODSCODE.ToString()].ToString();
                            CellRangeAddress region = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 1, 1);
                            CellRangeAddress region1 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 2, 2);
                            CellRangeAddress region2 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 6, 6);
                            CellRangeAddress region3 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 7, 7);
                            addrMachScheme.AddMergedRegion(region);
                            addrMachScheme.AddMergedRegion(region1);
                            addrMachScheme.AddMergedRegion(region2);
                            addrMachScheme.AddMergedRegion(region3);

                        }
                        else
                        {
                            endCount = k - 1;
                            if (hshTable.Contains(listReceiveMachScheme[k - 2].GOODSCODE.ToString()))
                            {
                                string aa = listReceiveMachScheme[k - 1].GOODSCODE.ToString();
                                staCount = (string)hshTable[listReceiveMachScheme[k - 2].GOODSCODE.ToString()].ToString();
                                CellRangeAddress region = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 1, 1);
                                CellRangeAddress region1 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 2, 2);
                                CellRangeAddress region2 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 6, 6);
                                CellRangeAddress region3 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 7, 7);
                                addrMachScheme.AddMergedRegion(region);
                                addrMachScheme.AddMergedRegion(region1);
                                addrMachScheme.AddMergedRegion(region2);
                                addrMachScheme.AddMergedRegion(region3);

                                if (!hshTable.Contains(listReceiveMachScheme[k - 1].GOODSCODE.ToString()))
                                {
                                    hshTable.Add(listReceiveMachScheme[k - 1].GOODSCODE.ToString(), k.ToString());
                                }
                            }
                        }
                    }

                }
            }

            #endregion

            #region 加工单净菜

            for (int k = 0; k < listReceiveMachSchemeJC.Count + 1; k++)
            {
                addrMachSchemeJC.CreateRow(k);
                addrMachSchemeJC.GetRow(k).Height = 400;

                if (k == 0)
                {
                    addrMachSchemeJC.GetRow(0).CreateCell(0).SetCellValue("库位");
                    addrMachSchemeJC.GetRow(0).CreateCell(1).SetCellValue("货号");
                    addrMachSchemeJC.GetRow(0).CreateCell(2).SetCellValue("品名");
                    addrMachSchemeJC.GetRow(0).CreateCell(3).SetCellValue("原材料库位");
                    addrMachSchemeJC.GetRow(0).CreateCell(4).SetCellValue("主、配、原料");
                    addrMachSchemeJC.GetRow(0).CreateCell(5).SetCellValue("半成品单位");
                    addrMachSchemeJC.GetRow(0).CreateCell(6).SetCellValue("请购份数");

                    addrMachSchemeJC.GetRow(0).CreateCell(7).SetCellValue("成品净含量");
                    addrMachSchemeJC.GetRow(0).CreateCell(8).SetCellValue("配方需要数量");
                    addrMachSchemeJC.GetRow(0).CreateCell(9).SetCellValue("每份量/克(净)");
                    addrMachSchemeJC.GetRow(0).CreateCell(10).SetCellValue("生成总量(净)");
                    addrMachSchemeJC.GetRow(0).CreateCell(11).SetCellValue("差异总量(净)");
                    addrMachSchemeJC.GetRow(0).CreateCell(12).SetCellValue("档口");



                }
                else if (k > 0)
                {

                    addrMachSchemeJC.GetRow(k).CreateCell(0).SetCellValue(listReceiveMachSchemeJC[k - 1].LOOKUP);
                    addrMachSchemeJC.GetRow(k).CreateCell(1).SetCellValue(listReceiveMachSchemeJC[k - 1].GOODSCODE);
                    addrMachSchemeJC.GetRow(k).CreateCell(2).SetCellValue(listReceiveMachSchemeJC[k - 1].GOODSNAME);
                    addrMachSchemeJC.GetRow(k).CreateCell(3).SetCellValue(listReceiveMachSchemeJC[k - 1].CUSTOMLOOKUP);
                    addrMachSchemeJC.GetRow(k).CreateCell(4).SetCellValue(listReceiveMachSchemeJC[k - 1].DGOODSNAME);
                    addrMachSchemeJC.GetRow(k).CreateCell(5).SetCellValue(listReceiveMachSchemeJC[k - 1].DPURUNITNAME);
                    addrMachSchemeJC.GetRow(k).CreateCell(6).SetCellValue(Convert.ToInt32(listReceiveMachSchemeJC[k - 1].ESTIMATENUMBER));
                    addrMachSchemeJC.GetRow(k).CreateCell(7).SetCellValue(listReceiveMachSchemeJC[k - 1].GOODSSPEC);
                    addrMachSchemeJC.GetRow(k).CreateCell(8).SetCellValue(Convert.ToDouble(listReceiveMachSchemeJC[k - 1].QTY));

                    addrMachSchemeJC.GetRow(k).CreateCell(9).SetCellValue(Convert.ToDouble(listReceiveMachSchemeJC[k - 1].DPKGLOSSQTY));
                    addrMachSchemeJC.GetRow(k).CreateCell(10).SetCellValue(Convert.ToDouble(listReceiveMachSchemeJC[k - 1].DPKGLOSSQTY * listReceiveMachSchemeJC[k - 1].ESTIMATENUMBER));
                    addrMachSchemeJC.GetRow(k).CreateCell(11).SetCellValue(Convert.ToDouble(listReceiveMachSchemeJC[k - 1].DPKGLOSSQTY * listReceiveMachSchemeJC[k - 1].ACTUALNUMBER));
                    addrMachSchemeJC.GetRow(k).CreateCell(12).SetCellValue(listReceiveMachSchemeJC[k - 1].GOODSDOOR);



                }
            }

            #endregion

            #region 加工单非净菜
            Hashtable hshTableFJC = new Hashtable(); //  创建哈希表 
            for (int k = 0; k < listReceiveMachSchemeFJC.Count + 1; k++)
            {
                addrMachSchemeFJC.CreateRow(k);
                addrMachSchemeFJC.GetRow(k).Height = 400;

                if (k == 0)
                {
                    addrMachSchemeFJC.GetRow(0).CreateCell(0).SetCellValue("库位");
                    addrMachSchemeFJC.GetRow(0).CreateCell(1).SetCellValue("货号");
                    addrMachSchemeFJC.GetRow(0).CreateCell(2).SetCellValue("品名");
                    //addrMachSchemeFJC.GetRow(0).CreateCell(3).SetCellValue("主、配、原料");
                    addrMachSchemeFJC.GetRow(0).CreateCell(3).SetCellValue("成品单位");
                    addrMachSchemeFJC.GetRow(0).CreateCell(4).SetCellValue("请购份数");

                    addrMachSchemeFJC.GetRow(0).CreateCell(5).SetCellValue("成品净含量");
                    //addrMachSchemeFJC.GetRow(0).CreateCell(8).SetCellValue("配方需要数量");
                    //addrMachSchemeFJC.GetRow(0).CreateCell(9).SetCellValue("每份量/克(净)");
                    //addrMachSchemeFJC.GetRow(0).CreateCell(10).SetCellValue("生成总量(净)");
                    //addrMachSchemeFJC.GetRow(0).CreateCell(11).SetCellValue("差异总量(净)");
                    //addrMachSchemeFJC.GetRow(0).CreateCell(12).SetCellValue("档口");



                }
                else if (k > 0)
                {

                    addrMachSchemeFJC.GetRow(k).CreateCell(0).SetCellValue(listReceiveMachSchemeFJC[k - 1].LOOKUP);
                    addrMachSchemeFJC.GetRow(k).CreateCell(1).SetCellValue(listReceiveMachSchemeFJC[k - 1].GOODSCODE);
                    addrMachSchemeFJC.GetRow(k).CreateCell(2).SetCellValue(listReceiveMachSchemeFJC[k - 1].GOODSNAME);
                    //addrMachSchemeFJC.GetRow(k).CreateCell(3).SetCellValue(listReceiveMachSchemeFJC[k - 1].DGOODSNAME);
                    addrMachSchemeFJC.GetRow(k).CreateCell(3).SetCellValue(listReceiveMachSchemeFJC[k - 1].UNITNAME);
                    addrMachSchemeFJC.GetRow(k).CreateCell(4).SetCellValue(Convert.ToInt32(listReceiveMachSchemeFJC[k - 1].ESTIMATENUMBER));
                    addrMachSchemeFJC.GetRow(k).CreateCell(5).SetCellValue(listReceiveMachSchemeFJC[k - 1].GOODSSPEC);
                    //addrMachSchemeFJC.GetRow(k).CreateCell(8).SetCellValue(Convert.ToDouble(listReceiveMachSchemeFJC[k - 1].QTY));

                    //addrMachSchemeFJC.GetRow(k).CreateCell(9).SetCellValue(Convert.ToDouble(listReceiveMachSchemeFJC[k - 1].DPKGLOSSQTY));
                    //addrMachSchemeFJC.GetRow(k).CreateCell(10).SetCellValue(Convert.ToDouble(listReceiveMachSchemeFJC[k - 1].DPKGLOSSQTY * listReceiveMachSchemeFJC[k - 1].ESTIMATENUMBER));
                    //addrMachSchemeFJC.GetRow(k).CreateCell(11).SetCellValue(Convert.ToDouble(listReceiveMachSchemeFJC[k - 1].DPKGLOSSQTY * listReceiveMachSchemeFJC[k - 1].ACTUALNUMBER));
                    //addrMachSchemeFJC.GetRow(k).CreateCell(12).SetCellValue(listReceiveMachSchemeFJC[k - 1].GOODSDOOR);





                    //if (k > 1)
                    //{
                    //    string staCount = "";
                    //    int endCount = 0;
                    //    if (listReceiveMachSchemeFJC[k - 1].GOODSCODE.ToString() == listReceiveMachSchemeFJC[k - 2].GOODSCODE.ToString())
                    //    {
                    //        if (!hshTableFJC.Contains(listReceiveMachSchemeFJC[k - 2].GOODSCODE.ToString()))
                    //        {
                    //            hshTableFJC.Add(listReceiveMachSchemeFJC[k - 2].GOODSCODE.ToString(), (k - 1).ToString());
                    //        }

                    //        endCount = k;
                    //        staCount = (string)hshTableFJC[listReceiveMachSchemeFJC[k - 1].GOODSCODE.ToString()].ToString();
                    //        CellRangeAddress region = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 1, 1);
                    //        CellRangeAddress region1 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 2, 2);
                    //        CellRangeAddress region2 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 5, 5);
                    //        CellRangeAddress region3 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 6, 6);
                    //        addrMachSchemeFJC.AddMergedRegion(region);
                    //        addrMachSchemeFJC.AddMergedRegion(region1);
                    //        addrMachSchemeFJC.AddMergedRegion(region2);
                    //        addrMachSchemeFJC.AddMergedRegion(region3);

                    //    }
                    //    else
                    //    {
                    //        endCount = k - 1;
                    //        if (hshTableFJC.Contains(listReceiveMachSchemeFJC[k - 2].GOODSCODE.ToString()))
                    //        {
                    //            string aa = listReceiveMachSchemeFJC[k - 1].GOODSCODE.ToString();
                    //            staCount = (string)hshTableFJC[listReceiveMachSchemeFJC[k - 2].GOODSCODE.ToString()].ToString();
                    //            CellRangeAddress region = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 1, 1);
                    //            CellRangeAddress region1 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 2, 2);
                    //            CellRangeAddress region2 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 5, 5);
                    //            CellRangeAddress region3 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 6, 6);
                    //            addrMachSchemeFJC.AddMergedRegion(region);
                    //            addrMachSchemeFJC.AddMergedRegion(region1);
                    //            addrMachSchemeFJC.AddMergedRegion(region2);
                    //            addrMachSchemeFJC.AddMergedRegion(region3);

                    //            if (!hshTableFJC.Contains(listReceiveMachSchemeFJC[k - 1].GOODSCODE.ToString()))
                    //            {
                    //                hshTableFJC.Add(listReceiveMachSchemeFJC[k - 1].GOODSCODE.ToString(), k.ToString());
                    //            }
                    //        }
                    //    }
                    //}

                }
            }

            #endregion



            #endregion

            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", NewTimeStamp("预估生产单"));
        }

    }
}
